//
//  TaoCanAnLiDataBaseHandle.m
//  Wedding fantastic
//
//  Created by ZLM on 15/7/6.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "TaoCanAnLiDataBaseHandle.h"

@implementation TaoCanAnLiDataBaseHandle
+(instancetype)shareDataBase{
    static TaoCanAnLiDataBaseHandle *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[TaoCanAnLiDataBaseHandle alloc]init];
    }
    return dataBase;
}

//1.导入数据库类库 libsqlite3.0
//2.创建单例类
//3.引入头文件 创建数据库对象 db

static sqlite3 *db = nil;

//4.打开数据库
-(void)openDB
{
    if (db != nil) {
        return;//如果数据库对象存在则不往下执行
    }
    
    //创建数据库文件路径
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath = [array lastObject];
    NSString *sqlitePath = [filePath stringByAppendingPathComponent:@"dataBase.sqlite"];
    NSLog(@"sqlitePath = %@",sqlitePath);
    
    //打开数据库函数 将数据库对象与数据库文件关联,并打开数据库
    int result = sqlite3_open(sqlitePath.UTF8String, &db);//** 要加&
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库成功");
    }else
    {
        NSLog(@"打开数据库失败");
    }
    
}
-(void)createTable
{
    //写建表的sql语句
    NSString *sql = @"CREATE TABLE IF NOT EXISTS TaoCan (number INTEGER PRIMARY KEY AUTOINCREMENT, cover_path TEXT, title TEXT, name TEXT UNIQUE)";
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
-(void)createTableAnLi
{
    //写建表的sql语句
    NSString *sql = @"CREATE TABLE IF NOT EXISTS AnLi (number INTEGER PRIMARY KEY AUTOINCREMENT, cover_path TEXT, title TEXT, name TEXT UNIQUE)";
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}

//插入数据
-(void)insert:(TaoCanAnLiModel *)TaoMod
{
    //SQL语句 number是主键:自动递增
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO TaoCan (cover_path, title, name) VALUES('%@','%@','%@')", TaoMod.cover_path, TaoMod.title,TaoMod.name];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        UIAlertView *alertV = [[UIAlertView alloc]initWithTitle:@"提示" message:@"收藏成功" delegate:self cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];
            [alertV show];
        NSLog(@"插入成功");
    }else
    {
        UIAlertView *alertV = [[UIAlertView alloc]initWithTitle:@"提示" message:@"已收藏" delegate:self cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];
        
                [alertV show];

        NSLog(@"插入失败");
    }
    
}
-(void)insertAnLi:(TaoCanAnLiModel *)AnLiMod
{
    //SQL语句 number是主键:自动递增
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO AnLi (cover_path, title, name) VALUES('%@','%@','%@')", AnLiMod.cover_path, AnLiMod.title,AnLiMod.name];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        UIAlertView *alertV = [[UIAlertView alloc]initWithTitle:@"提示" message:@"收藏成功" delegate:self cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];
        [alertV show];
        NSLog(@"插入成功");
    }else
    {
        UIAlertView *alertV = [[UIAlertView alloc]initWithTitle:@"提示" message:@"已收藏" delegate:self cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];
        
        [alertV show];
        
        NSLog(@"插入失败");
    }
    
}
//修改
-(void)updateTable:(TaoCanAnLiModel *)TaoMod number:(NSInteger)number
{
    NSString *sql = [NSString stringWithFormat:@"UPDATE TaoCan SET cover_path = '%@', title = '%@', name = '%@' WHERE number = '%ld'",TaoMod.cover_path, TaoMod.title, TaoMod.name, (long)number];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
    }else
    {
        NSLog(@"修改失败");
    }
    
}
//删除
-(void)deleteTable:(NSInteger)number
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM TaoCan WHERE number = '%ld'",(long)number];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
        
    }else
    {
        NSLog(@"删除失败");
    }
}
//根据套餐name删除
-(void)deleteTableByName:(NSString *)name{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM TaoCan WHERE name = '%@'",name];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除ByName成功");
        
    }else
    {
        NSLog(@"删除ByName失败");
    }


}
//根据案例name删除
-(void)deleteTableByAnLiName:(NSString *)anliname{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM AnLi WHERE name = '%@'",anliname];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除ByAnLiName成功");
        
    }else
    {
        NSLog(@"删除ByAnLiName失败");
    }
    
    
}
//查询所有
-(NSMutableArray *)selectAllTao
{
    NSString *sql = @"SELECT * FROM TaoCan";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt= nil;
    
    NSMutableArray *array = [NSMutableArray array];
    
    //查询准备操作
    //参数3.-1代表不限制sql语句长度
    //主要作用是将数据库对象db,sql语句,数据库跟指针关联到一起,以便数据库进行查询操作.
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            //取出表里每行中的每个字段数据
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *cover_path = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            const unsigned char *name = sqlite3_column_text(stmt, 3);
            
            TaoCanAnLiModel *tao = [[TaoCanAnLiModel alloc]init];
            tao.cover_path = [NSString stringWithUTF8String:(const char *)cover_path];
            tao.title = [NSString stringWithUTF8String:(const char *)title];
            tao.name = [NSString stringWithUTF8String:(const char *)name];
            
            
            tao.number = number;
            [array addObject:tao];
        }
        return array;
    }else
    {
        NSLog(@"准备失败");
        return nil;//或者return array
    }
}
-(NSMutableArray *)selectAllAn
{
    NSString *sql = @"SELECT * FROM AnLi";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt= nil;
    
    NSMutableArray *array = [NSMutableArray array];
    
    //查询准备操作
    //参数3.-1代表不限制sql语句长度
    //主要作用是将数据库对象db,sql语句,数据库跟指针关联到一起,以便数据库进行查询操作.
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            //取出表里每行中的每个字段数据
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *cover_path = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            const unsigned char *name = sqlite3_column_text(stmt, 3);
            
            TaoCanAnLiModel *anli = [[TaoCanAnLiModel alloc]init];
            anli.cover_path = [NSString stringWithUTF8String:(const char *)cover_path];
            anli.title = [NSString stringWithUTF8String:(const char *)title];
            anli.name = [NSString stringWithUTF8String:(const char *)name];
            
            
            anli.number = number;
            [array addObject:anli];
        }
        return array;
    }else
    {
        NSLog(@"准备失败");
        return nil;//或者return array
    }
}

//删除表
-(void)dropTable
{
    NSString *sql = @"DROP TABLE TaoCan";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else
    {
        NSLog(@"删除表失败");
    }
}

@end
